package cn.com.libertymutual.sp.dao;

import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSysHotarea;

@Repository
public interface TbSysHotareaDao extends PagingAndSortingRepository<TbSysHotarea, Integer>, JpaSpecificationExecutor<TbSysHotarea>
{

	@Query("select t from TbSysHotarea t where t.branchCode = ?1 and t.status = '1' ")
	Page<TbSysHotarea> findByBranchCode(String branchCode,Pageable pageable);

	TbSysHotarea findByBranchCodeAndAreaCode(String branchCode, String areaCode);

	@Query("select t from TbSysHotarea t where t.status = '1' ")
	Page<TbSysHotarea> findAllArea(Pageable pageable);
	
	@Query("select t.areaCode from TbSysHotarea t where t.status = '1' ")
	List<String> findAllAreaCode();
	
	@Query("select t from TbSysHotarea t where t.status = '1' ")
	List<TbSysHotarea> findAllArea();
	
	@Transactional
	@Modifying
	@Query("update  TbSysHotarea  set status = '0' where branchCode = ?1 and areaCode = ?2 and status = '1' ")
	int updateStatus(String branchCode, String areaCode);

	@Query("select t from TbSysHotarea t where t.branchCode = ?1 and t.status = '1' ")
	List<TbSysHotarea> findByBranchCode(String branchCode);

	@Query("select t from TbSysHotarea t where t.areaCode = ?1 and t.status = '1' ")
	List<TbSysHotarea> findLikeBranchCodeLike(String areaCode);

	@Query("select t from TbSysHotarea t where t.branchCode = ?1 and t.status = '1' ")
	List<TbSysHotarea> findLikeBranchCode(String branchCode);
	
	@Query("select t from TbSysHotarea t where t.areaName = ?1 and t.status = '1' ")
	TbSysHotarea findByAreaName(String areaName);

	@Transactional
	@Modifying
	@Query("update  TbSysHotarea  set agreementNo = ?1,saleCode = ?2 ,saleName = ?3, invoiceStatus= ?4, insurancePolicyStatus = ?5 ,contactMobile = ?6,contactEmail = ?7 where branchCode = ?8 ")
	void updateAgreementNo(String agreementNo, String saleCode, String saleName, String invoiceStatus,String insurancePolicyStatus,String contactMobile,String contactEmail,String branchCode);

//	@Query("select t from TbSysHotarea t where  t.status = '1' ")
//	List<TbSysHotarea> findAllArea();

	@Query("select t from TbSysHotarea t where t.areaCode = ?1 and t.status = '1' ")
	TbSysHotarea findByAreaCode(String code);

	
	@Query("from TbSysHotarea where status = '1' GROUP BY branchCode ")
	List<TbSysHotarea> findAllOne();

	@Query("select t from TbSysHotarea t where t.agreementNo = ?1 and t.areaCode = ?2 and t.status = '1' ")
	TbSysHotarea findByAgreementNoAndAreaCode(String agreementNo,String areaCode);

	@Query("select t from TbSysHotarea t where t.id = :id and t.status = '1' ")
	Optional<TbSysHotarea> findById(@Param("id") Integer id);
	
}
